package cn.edu.cdp.jxgl_springboot_web.dao;


import cn.edu.cdp.jxgl_springboot_web.domain.Student;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface AdminDao {
    // 作业要求
    //（1）	查询XXX同学各科成绩：学号，姓名，专业名称，课程名称，成绩
    @Select("select s.student_id studentId, s.name studentName, m.name majorName, c.name courseName, y.score " +
            "from student s, major m, course c, study y " +
            "where s.major_id=m.major_id and y.student_id=s.student_id and y.course_id=c.course_id " +
            "and (s.student_id=#{text} or s.name like concat('%', #{text}, '%'))")
    List<Map<String, Object>> scoreByStudentIdOrName(String text);


    //（2）	按专业统计学生人数：专业，学生人数
    @Select("select m.name majorName, count(*) number from student s left join major m " +
            "on s.major_id=m.major_id group by m.major_id")
    List<Map<String, Object>> statisticStudentNumberByMajor();


    //（3）	查询某门课程成绩单：学号，姓名，专业名称，成绩
    @Select("select s.student_id studentId, s.name studentName, m.name majorName, c.name courseName, y.score " +
            "from student s, major m, course c, study y " +
            "where s.major_id=m.major_id and y.student_id=s.student_id and y.course_id=c.course_id " +
            "and (c.course_id=#{text} or c.name like concat('%', #{text}, '%'))")
    List<Map<String, Object>> scoreByCourseIdOrName(String text);


    //（4）	查询学生总学分，不及格不计学分：学号，姓名，专业名称，总学分
    @Select("select s.student_id studentId, s.name studentName, m.name majorName, sum(if(y.score >= 60, c.credit, 0)) sumCredit " +
            "from student s, major m, course c, study y " +
            "where s.major_id=m.major_id and y.student_id=s.student_id and y.course_id=c.course_id " +
            "group by s.student_id")
    List<Map<String, Object>> totalCredit();


    //（5）	查询不及格学生信息：学号，姓名，专业名称，补考课程名称
    @Select("select s.student_id studentId, s.name studentName, m.name majorName, c.name courseName " +
            "from student s, major m, course c, study y " +
            "where s.major_id=m.major_id and y.student_id=s.student_id and y.course_id=c.course_id and y.score < 60")
    List<Map<String, Object>> failExam();


    //（6）	查询奖学金学生信息（要求：无不及格记录，且平均分在85分以上）：学号，姓名，专业名称，平均分
    @Select("select s.student_id studentId, s.name studentName, m.name majorName, round(avg(y.score), 2) avgScore " +
            "from student s, major m, study y " +
            "where s.student_id=y.student_id and s.major_id=m.major_id " +
            "group by s.student_id " +
            "having min(y.score)>=60 and avg(y.score) >=85")
    List<Map<String, Object>> gainScholarship();


    // 查询时能不能先选学院，再选专业，然后可以输入姓名或学号进行查询，类似学于校网站的操作
    // 用于学生面板
        @Select("select s.student_id studentId, s.name, s.sex, s.birthday, s.cee_score ceeScore, s.nation, s.native_place nativePlace, s.resume, s.profile, s.major_id majorId, m.name majorName " +
            "from student s left join major m on s.major_id = m.major_id left join college c on m.college_id=c.college_id " +
            "where if(#{collegeName}='', true, c.name=#{collegeName}) " +
            "and if(#{majorName}='', true, m.name=#{majorName}) " +
            "and (s.student_id=#{studentIdOrName} or s.name like concat('%', #{studentIdOrName}, '%'))")
    List<Student> filterStudent(@Param("collegeName") String collegeName, @Param("majorName") String majorName, @Param("studentIdOrName") String studentIdOrName);
}
